from sinotrans.core import FileProcessor, ExcelProcessor
from sinotrans.utils import Logger, GlobalThreadPool
from openpyxl import load_workbook
from datetime import datetime
import concurrent.futures
import openpyxl.utils
import traceback
import openpyxl
import warnings
import sys
import os

warnings.filterwarnings("ignore", category=UserWarning, module="openpyxl")

class AutoCfs:
    # 配置路径参数：os.path.abspath(__file__)
    # 打包替换启动路径为：root_path3 = os.path.dirname(os.path.realpath(sys.executable))
    POD_NAME="POD" # clp中起运港名称不可更改，用于
    TDH_NAME="提单号"
    DEFAULT_SPLITTER = '#'

    def __init__(self):
        # 初始化路径配置os.path.dirname(os.path.realpath(sys.executable))os.path.abspath(__file__)
        self.timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
        self.current_dir = os.path.dirname(os.path.abspath(__file__))
        self._init_paths()
        self._init_logger()
        self._init_thread_pool()
        self._init_styles()

    def _init_paths(self):
        """初始化所有路径配置"""
        self.to_path = os.path.join(self.current_dir, 'to')
        self.config_path = os.path.join(self.current_dir, "conf")
        self.clp_path = os.path.join(self.current_dir, "clp")
        self.template_path = os.path.join(self.current_dir, "template")
        self.target_path = os.path.join(self.current_dir, "target")

        self.fixed_mapping_file = os.path.join(self.config_path, "fixed_mapping.txt")
        self.pod_config_file = os.path.join(self.config_path, "pod_config.txt")
        self.shipper_config_file = os.path.join(self.config_path, "shipper_config.txt")
        self.clp_mapping_file = os.path.join(self.config_path, "clp_mapping.txt")
        self.to_mapping_file = os.path.join(self.config_path, "to_mapping.txt")

        FileProcessor.ensure_directories_exist(directories = [
            self.template_path,
            self.clp_path,
            self.to_path,
            self.config_path,
            self.target_path
        ])

    def _init_logger(self):
        """初始化日志系统"""
        debug_path = os.path.join(self.current_dir, "logs")
        Logger(debug_path=debug_path)
    def _init_thread_pool(self):
        """初始化全局线程池"""
        GlobalThreadPool.initialize(
            max_workers=16,
            thread_name_prefix='AutoCfsThreadPool'
        )
    def _init_styles(self):
        """初始化Excel样式"""
        self.header_style = openpyxl.styles.NamedStyle(name="header_style")
        self.header_style.font = openpyxl.styles.Font(name="Calibri", bold=True, color="FFFFFF")
        self.header_style.fill = openpyxl.styles.PatternFill(fill_type="solid", fgColor="4F81BD")
    
    def _style_apply(self, output_ws):
        output_ws.freeze_panes = "A2"
        for cell in output_ws[1]:
            cell.style = self.header_style
        # 设置固定列宽（所有列宽度为15）
        # 生成 A 到 BG 的列字母（A=1, B=2, ..., BG=59）
        columns = [openpyxl.utils.get_column_letter(i) for i in range(1, 59 + 1)]  # BG 是第 59 列
        list(map(lambda col: setattr(output_ws.column_dimensions[col], 'width', 20), columns))
        # 隔行填充背景色
        fill = openpyxl.styles.PatternFill(fill_type='solid', start_color='C8D7E9', end_color='C8D7E9')
        for row_num in range(2, output_ws.max_row + 1):  # 从第2行开始（第1行是表头）
            if row_num % 2 == 0:
                for col_num in range(1, output_ws.max_column + 1):
                    cell = output_ws.cell(row=row_num, column=col_num)
                    cell.fill = fill
                    cell.font = openpyxl.styles.Font(name='Calibri', size=11)
            else:
                for col_num in range(1, output_ws.max_column + 1):
                    cell = output_ws.cell(row=row_num, column=col_num)
                    cell.font = openpyxl.styles.Font(name='Calibri', size=11)
    def _column_mapping(self, pod_conf, shipper_conf, row, map, index):
        mapped_row = {}
        try:
            for src_col, rules in map.items():
                for rule in rules:
                    raw_value = row.get(rule.field_name, None)
                    if raw_value:
                        if raw_value in pod_conf.keys():
                            # TODO 该举动会将源文件中所有能转换为五字代码和船司代理的全转换为五字代码，不严谨，但是目前我没有想出更好的办法
                            raw_value = pod_conf.get(raw_value).field_name
                        if raw_value in shipper_conf.keys():
                            # TODO 该举动会将源文件中所有能转换为五字代码和船司代理的全转换为五字代码，不严谨，但是目前我没有想出更好的办法
                            raw_value = shipper_conf.get(raw_value).field_name
                        mapped_row[rule.field_name] = rule.map_action(raw_value, index)
            return mapped_row
        except Exception as e:
            Logger.error(f"❌ mapping映射失败: {str(e)}")
            raise
    def _create_target_file(self, template_file_name, target_file_name):
        # 查找模板文件
        template_file = os.path.join(self.template_path, template_file_name + ".xlsx")
        Logger.info(f"📄 已找到模板【{template_file_name}】")

        target_file = FileProcessor.create_newfile_by_template(template_file, target_file_name)
        output_wb = load_workbook(target_file)
        output_ws = output_wb["请填写贵司的公司全称"]#.active

        Logger.info(f"✨【{target_file}】文件创建成功")
        return output_wb, output_ws
    def _get_template_name(self, pod_conf, pod_name, content_map):
        """
        获取目的港对应的五字代码——模板文件名，如CLVAP
        更新当前装箱计划中的目的港的初始值为映射后的五字代码，方便后续作其他字段的映射处理。
        """
        if pod_name is None or pod_name not in pod_conf.keys():
            raise Exception(f"❌ {pod_name}对应的五字代码未找到！")
        template_file_name = pod_conf.get(pod_name).field_name
        content_map[self.POD_NAME] = template_file_name
        return template_file_name
    def process_clp_file(self, clp, content_map, key_to_rows, pod_conf, shipper_conf, fixed_map, clp_map, to_map):
        try:
            Logger.info(f"--------------------------------------")
            Logger.info(f"📥 正在处理【{clp}】装箱计划文件")
            template_file_name = self._get_template_name(pod_conf, content_map[self.POD_NAME], content_map)
            
            target_file = os.path.join(self.target_path, os.path.basename(clp) + "_" + self.timestamp + ".xlsx")
            template_file = os.path.join(self.template_path, template_file_name + ".xlsx")
            output_wb, output_ws = self._create_target_file(template_file_name, target_file)
            headers = [cell.value for cell in output_ws[1]]
            # 创建新行{key_tuple:row}
            base_data = {}
            gw_list = list(map(lambda x: float(x.strip()), content_map.get("毛重").split(self.DEFAULT_SPLITTER)))
            cbm_list = list(map(lambda x: float(x.strip()), content_map.get("体积").split(self.DEFAULT_SPLITTER)))
            for i in range(min(len(gw_list),len(cbm_list))):
                key = (gw_list[i], cbm_list[i])
                if key in key_to_rows.keys():
                    Logger.debug(f"🔍 已找到{key}对应的舱单数据")
                    base_row = ExcelProcessor.read_excel_row(template_file, "请填写贵司的公司全称", 0) # {header: '' for header in headers}
                    base_row.update(ExcelProcessor.fixed_mapping(fixed_map))
                    base_row.update(self._column_mapping(pod_conf, shipper_conf, content_map, clp_map, i))
                    base_row.update(ExcelProcessor.column_mapping(key_to_rows.get(key), to_map))

                    base_data[key] = base_row
            # 排序按表头排序
            ordered_rows = ExcelProcessor.sort_generated_rows(base_data.values(), headers)
            list(map(lambda row: output_ws.append(row), ordered_rows))
            Logger.info(f"✅ 工作表【请填写贵司的公司全称】处理完成，共更新 {len(base_data.values())} 行数据")
            # 调整格式
            self._style_apply(output_ws)
            # 写入文件
            output_wb.save(target_file)
            Logger.info(f"💾 结果文件保存成功: {target_file}")
            return True
        except Exception as e:
            Logger.error(f"❌ 装箱计划{clp}处理失败: {str(e)}")
            Logger.debug(f"{traceback.format_exc()}")
            if os.path.exists(target_file):
                os.remove(target_file)
                Logger.error("已删除不完整的结果文件")
            return False
    def run(self):
        try:
            # 读取两种映射关系：字段名映射和模板值映射
            clp_map = FileProcessor.parse_mapping_dict_of_list(self.clp_mapping_file,':', '|', ',', '=')   # 字段名映射
            to_map = FileProcessor.parse_mapping_dict_of_list(self.to_mapping_file,':', '|', ',', '=')   # 字段名映射
            fixed_map = FileProcessor.parse_mapping_dict(self.fixed_mapping_file,':', '|', ',', '=')   # 模板值映射
            pod_conf = FileProcessor.parse_mapping_dict(self.pod_config_file,':', '|', ',', '=')
            shipper_conf = FileProcessor.parse_mapping_dict(self.shipper_config_file,':', '|', ',', '=')
            Logger.info("✅ 读取映射文件成功！")
        except Exception as e:
            raise RuntimeError(f"❌ 映射文件读取失败: {str(e)}")
        
        try:
            # 获取装箱计划、舱单的文件名列表
            to_files = FileProcessor.read_files(self.to_path, [".xlsx",".xls"])
            clp_files = FileProcessor.read_files(self.clp_path, [".xlsx",".xls"])
            Logger.info("✅ 获取装箱计划、舱单的文件成功！")
        except Exception as e:
            raise RuntimeError(f"❌ 获取装箱计划、舱单的文件失败: {str(e)}")
        
        try:
            # 根据映射文件中的读取规则，提取装箱计划、舱单对应字段值,已经做好映射的提取了。
            # to_files = parse_excel_files(to_files, to_mapping)
            excel_processor = ExcelProcessor()
            clp_file_content_map = excel_processor.parse_excel_files(files=clp_files, map=clp_map, file_type="装箱计划", progress=None, key_field_name=None)
            to_file_content_map = excel_processor.parse_excel_files(files=to_files, map=to_map, file_type="舱单", progress=None, key_field_name=None)
            Logger.info("✅ 提取装箱计划、舱单对应字段值成功！")
        except Exception as e:
            raise RuntimeError(f"❌ 提取装箱计划、舱单对应字段值失败: {str(e)}")
        
        try:
            # 读取to_file_content_map所有文件，将所有行存入内存key_rows中
            key_to_rows = {}
            for to_f in to_files:
                new_data = ExcelProcessor.load_excel_to_K_V(to_f, ["毛重", "体积"])
                if set(new_data.keys()) & set(key_to_rows.keys()):
                    Logger.info(f"⚠️ 存在重复基准数据！")
                for key, data_content in new_data.items():
                    data_content.update(ExcelProcessor.column_mapping(data_content, to_map))
                    new_data[key] = data_content
                key_to_rows.update(new_data)
            Logger.info(f"📥 已加载 {len(key_to_rows)} 条有效舱单基准数据")
        except Exception as e:
            raise RuntimeError(f"❌ 加载舱单文件失败: {str(e)}")
        
        try:
            # success_flags = []
            # # 一个装箱计划对应一个文件
            # for clp, content_map in clp_file_content_map.items():
            #     success_flags.append(
            #         self.process_clp_file(clp, content_map, key_to_rows, pod_conf, shipper_conf, fixed_map, clp_map, to_map)
            #     )
            # return all(success_flags)
            # 使用线程池并发处理文件
            # data_lock = threading.Lock()
            with GlobalThreadPool.get_executor() as executor:
                futures = [
                    executor.submit(self.process_clp_file, clp, content_map, key_to_rows, pod_conf, shipper_conf, fixed_map, clp_map, to_map) 
                        for clp, content_map in clp_file_content_map.items()
                        ]
            
            done, not_done = concurrent.futures.wait(futures, timeout = 60)

            # 等待所有任务完成
            for future in futures:
                future.result()  # 获取结果，触发可能的异常
            return True
        except Exception as e:
            Logger.error(f"❌ 主流程执行失败: {str(e)}")
            Logger.debug(f"{traceback.format_exc()}")
            return False
        finally:
            GlobalThreadPool.shutdown()

if __name__ == "__main__":
    processor = AutoCfs()
    if processor.run():
        Logger.info("🎉 自动化处理完成！")
    else:
        Logger.error("❌ 处理过程中存在错误")
    input("按回车键退出...")